Steps to Setup Database Link in PostgreSQL
Database Link in PostgreSQL enables a database user to access a table present on a different PostgreSQL cluster. It provides a functionality in PostgreSQL similar to that of DB Link in Oracle, Linked Server in SQL Server and Federated Table in MySQL. In previous post we saw How to Setup foreign data wrapper (postgres_fdw) Click here to read more. In this post we will see Steps to Setup DB Link in PostgreSQL.
For this demonstration, we’ll use the databases local_db and remote_db. We will access a table employee in remote_db from local_db through DB LINK.
Below are the high-level steps we will follow to set up DB Link
1. Setup Remote DB & pg_hba.conf
2. Create the DBLink Extension
3. Create the Foreign Server
4. Create User Mapping
5. Grant the Local User Access to the Foreign Server
6. Establish connection
Below are setup details and the same will be used in this demonstration.
| Sr. No. | DB Name | User Name | Hostname | IP | Port |
| 1 | local_db | app_user | test-machine01 | 192.168.114.177 | 5432 |
| 2 | remote_db | fdw_user | test-machine02 | 192.168.114.176 | 5432 |
Step 1. Setup Remote DB & pg_hba.conf: In this step, we will create Database remote_db & User fdw_user. DB remote_db will be used in a later step to create a Foreign Server and user fdw_user will be used in a later step to Create User Mapping. Make sure to update file pg_hba.conf to allow PostgreSQL DB connection from Server: test-machine01. We have added the line “host all all 192.168.114.0/24 scram-sha-256” as we are using the ” 192.168.114.0/24 ” as a subnet.
[postgres@test-machine02]
[postgres@test-machine02]psql
psql (13.2)
Type "help" for help.
postgres=#
postgres=#
postgres=# create database remote_db;
CREATE DATABASE
postgres=#
postgres=# CREATE USER fdw_user WITH ENCRYPTED PASSWORD 'secret';
CREATE ROLE
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
user_test | Superuser | {} |
fdw_user | | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
repluser | Replication | {} |
postgres=#
postgres=# \q
[postgres@test-machine02]psql -h test-machine02 -U fdw_user remote_db
Password for user fdw_user:
psql (13.2)
Type "help" for help.
remote_db=> create table employee (id int, first_name varchar(20), last_name varchar(20));
CREATE TABLE
remote_db=> insert into employee values (1,'jobin','augustine'),(2,'avinash','vallarapu'),(3,'fernando','camargos');
INSERT 0 3
remote_db=> \dt employee
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | employee | table | fdw_user
(1 row)
remote_db=> select count(*) from employee;
count
-------
3
(1 row)
remote_db=>
[postgres@test-machine02]pwd
/var/lib/pgsql/13/data
[postgres@test-machine02]cat pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.114.0/24 scram-sha-256
[postgres@test-machine02]
Step 2. Create the DBLink Extension: Use psql command to create an extension to create the dblink extension. If you receive the error “ERROR: could not open extension control file dblink.control: No such file or directory“. You need to install postgresql13-contrib package. Use OS yum install command to install the missing PostgreSQL package. Once the package is installed try to create a dblink extension again. Please note all below commands we are executing in DB: local_db as we want to avoid using default postgres DB.
[root@test-machine01 ~]# su - postgres
Last login: Fri Oct 1 12:24:24 +03 2021 on pts/0
[postgres@test-machine01]
[postgres@test-machine01] psql local_db
psql (9.2.24, server 13.4)
WARNING: psql version 9.2, server version 13.0.
Some psql features might not work.
Type "help" for help.
local_db=# create extension dblink;
ERROR: could not open extension control file "/usr/pgsql-13/share/extension/dblink.control": No such file or directory
local_db=#
[root@test-machine01 extension]# yum install postgresql13-contrib
Loaded plugins: langpacks, ulninfo
Dependencies Resolved
=============================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================
Installing:
postgresql13-contrib x86_64 13.4-1PGDG.rhel7 pgdg13 609 k
Updating for dependencies:
postgresql13 x86_64 13.4-1PGDG.rhel7 pgdg13 1.4 M
postgresql13-libs x86_64 13.4-1PGDG.rhel7 pgdg13 381 k
postgresql13-server x86_64 13.4-1PGDG.rhel7 pgdg13 5.4 M
Transaction Summary
=============================================================================================================================================================================
Install 1 Package
Upgrade ( 3 Dependent packages)
Verifying : postgresql13-13.1-1PGDG.rhel7.x86_64 5/7
Verifying : postgresql13-libs-13.1-1PGDG.rhel7.x86_64 6/7
Verifying : postgresql13-server-13.1-1PGDG.rhel7.x86_64 7/7
Installed:
postgresql13-contrib.x86_64 0:13.4-1PGDG.rhel7
Dependency Updated:
postgresql13.x86_64 0:13.4-1PGDG.rhel7 postgresql13-libs.x86_64 0:13.4-1PGDG.rhel7 postgresql13-server.x86_64 0:13.4-1PGDG.rhel7
Complete!
[root@test-machine01 extension]#
local_db=#
local_db=# create extension dblink;
CREATE EXTENSION
local_db=#
local_db=#
local_db=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+--------------------------------------------------------------
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers
(3 rows)
local_db=# select * from pg_extension;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------+----------+--------------+----------------+------------+-----------+--------------
14161 | plpgsql | 10 | 11 | f | 1.0 | |
26435 | postgres_fdw | 10 | 2200 | t | 1.0 | |
26444 | dblink | 10 | 2200 | t | 1.2 | |
(3 rows)
local_db=#
Step 3. Create the Foreign Server: Now we can create a server definition. This foreign server is created using the connection details of the remote server running on host “test-machine02“. Let’s name the foreign server as “testmachine02_dblink “. Use \des to list foreign servers or query view pg_foreign_server.
local_db=# create server testmachine02_dblink foreign data wrapper dblink_fdw options (dbname 'remote_db', host 'test-machine02', port '5432');
CREATE SERVER
local_db=#
local_db=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
----------------------+----------+----------------------+---------------------+------+---------+----------------------------------------------------------+-------------
testmachine02_dblink | postgres | dblink_fdw | | | | (dbname 'remote_db', host 'test-machine02', port '5432') |
testmachine02_fdw | postgres | postgres_fdw | postgres=U/postgres+| | | (dbname 'remote_db', host 'test-machine02', port '5432') |
| | | app_user=U/postgres | | | |
(2 rows)
local_db=#
local_db=# select * from pg_foreign_server;
oid | srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
-------+----------------------+----------+--------+---------+------------+-------------------------------------------+--------------------------------------------------
26439 | testmachine02_fdw | 10 | 26438 | | | {postgres=U/postgres,app_user=U/postgres} | {dbname=remote_db,host=test-machine02,port=5432}
26492 | testmachine02_dblink | 10 | 26489 | | | | {dbname=remote_db,host=test-machine02,port=5432}
(2 rows)
local_db=#
Step 4. Create User Mapping: Create a mapping on the local database: local_db user app_user to target database: remot_db user fdw_user.
local_db=#
local_db=# create user mapping for app_user server testmachine02_dblink options (user 'fdw_user', password 'secret');
CREATE USER MAPPING
local_db=#
local_db=# select * from pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+----------------------+--------+----------+---------------------------------
26440 | 26439 | testmachine02_fdw | 26421 | app_user | {user=fdw_user,password=secret}
26493 | 26492 | testmachine02_dblink | 26421 | app_user | {user=fdw_user,password=secret}
(2 rows)
local_db=#
local_db=#
Step 5. Grant the Local User Access to the Foreign Server: Grant our local user app_user access to the foreign server testmachine02_dblink. Without the below permission, you will receive the error “ERROR: permission denied for foreign server testmachine02_dblink” if you try to create a connection as we are doing in Step 6.
local_db=#
local_db=# grant usage on foreign server testmachine02_dblink to app_user;
GRANT
local_db=#
Step 6. Establish connection: Establish a connection to the foreign server using the dblink_connect() function. Once the connection is established try to access Table: employee from remote_db.
local_db=# \quit
[postgres@test-machine01]
[postgres@test-machine01] psql -h test-machine01 -U app_user local_db
Password for user app_user:
psql (9.2.24, server 13.4)
WARNING: psql version 9.2, server version 13.0.
Some psql features might not work.
Type "help" for help.
local_db=>
local_db=> select dblink_connect('conn_dblink','testmachine02_dblink');
dblink_connect
----------------
OK
(1 row)
local_db=> select * from dblink ('conn_dblink','select * from employee') as test_dblink (id int, first_name varchar(20), last_name varchar(20));
id | first_name | last_name
----+------------+-----------
1 | jobin | augustine
2 | avinash | vallarapu
3 | fernando | camargos
(3 rows)
local_db=>
This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.
Related Articles
- Access the MSSQL database from PostgreSQL using TDS Foreign Data Wrapper (TDS_FDW)
- Access MySql database from PostgreSQL using MySQL Foreign Data Wrapper (MYSQL_FDW)
- Configure and monitor using pg_profile in PostgreSQL
- Monitor PostgreSQL Cluster using pgCenter
- Install and Configure pg_repack in PostgreSQL
Thank you